﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class PositionDAL : IPositionDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable ListPositions()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL = "SELECT p.POS_SEQ"
                    + ", CONCAT(p.POS_REF, ' - ', p.POS_NAME, '\t', coalesce(ex.exec_pos_name, ''), '\t', et.emp_type_name) AS POS_TEXT"
                    + " FROM HRM_MP_POSITIONS p"
                    + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                    + " LEFT JOIN hrm_ps_employee_types et ON p.emp_type_seq = et.emp_type_seq"
                    + " WHERE p.RECORD_STATUS not like @DeleteRecord"
                    + " ORDER BY p.POS_REF";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public DataSet LoadPositions(PositionInfo posInfo)
        {
            int jsSeq = 0;
            int etSeq = 0;
            DataSet ds;

            if (posInfo.JobSite != null)
            {
                if (posInfo.JobSite.Sequence > 0)
                    jsSeq = Convert.ToInt32(posInfo.JobSite.Sequence.ToString());
            }
            if (posInfo.EmployeeType != null)
                etSeq = posInfo.EmployeeType.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", posInfo.Sequence);
                dbManager.AddParameters(2, "@JobSite", "%/" + jsSeq.ToString() + "/%");
                dbManager.AddParameters(3, "@EmpTypeSeq", etSeq);
                dbManager.AddParameters(4, "@SearchText", posInfo.SearchText);

                string strSQL = "SELECT p.POS_SEQ, p.POS_REF, p.POS_NAME, p.JOB_DESCRIPTION"
                                + ", p.DATE_DEFINED, p.DATE_MONEY_SUPPORT, p.SALARY_QUOTA, p.SALARY_MAX, p.SALARY_MIN"
                                + ", p.FIELD_POS_SEQ, p.EXEC_POS_SEQ, p.EMP_TYPE_SEQ, p.JOB_SITE_SEQ, p.upper_pos_seq"
                                + ", p.working_upper_pos_seq , p.POS_STATUS " 
                                + ", p.REMARKS, p.RECORD_STATUS, p.CREATED_BY, p.UPDATED_BY"
                                + ", CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_TEXT"
                                + ", CONCAT(p.POS_REF, ' - ', p.POS_NAME, ' (', et.emp_type_name , ')') AS POS_EMP_TYPE_TEXT"
                                + ", pt.POS_TYPE_SEQ "
                                + ", f.FIELD_POS_NAME, f.FIELD_POS_LINE"
                                + ", pt.POS_TYPE_NAME, c.CLASS_NAME, c.CLASS_SEQ"
                                + ", ex.EXEC_POS_NAME, j.JOB_SITE_NAME, j.JOB_SITE_REF"
                                + ", ps.STATUS_NAME AS POS_STATUS_NAME"
                                + ", pp.POS_NAME AS POS_UPPER_NAME"
                                + ", CONCAT(pp.POS_REF, ' - ', pp.POS_NAME) AS POS_UPPER_TEXT"
                                + " FROM HRM_MP_POSITIONS p"
                                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                                + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                                + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                                + " LEFT JOIN hrm_ps_employee_types et ON p.emp_type_seq = et.emp_type_seq"
                                + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS sp ON f.CLASS_SPEC_SEQ = sp.CLASS_SPEC_SEQ"
                                + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ" 
                                + " LEFT JOIN HRM_MP_POSITION_STATUSES ps ON p.POS_STATUS = ps.STATUS_SEQ"
                                + " LEFT JOIN HRM_MP_POSITIONS pp ON p.upper_pos_seq = pp.POS_SEQ"
                                + " LEFT JOIN hrm_mp_class_spec_dtls csd on sp.class_spec_seq = csd.class_spec_seq"
                                + " LEFT JOIN hrm_mp_classes c on csd.class_seq = c.class_seq "
                                //+ " LEFT JOIN hrm_mp_classes c ON c.class_seq = p.class_seq"                                
                                + " WHERE p.RECORD_STATUS = @ActiveRecord"
                                + (posInfo.Sequence > 0 ? " AND p.POS_SEQ = @Sequence" : "")
                                + (etSeq > 0 ? " AND p.EMP_TYPE_SEQ = @EmpTypeSeq" : "")
                                + (jsSeq > 0 ? " AND j.JOB_SITE_FULL_PATH LIKE @JobSite" : "")
                    //+ " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                    //+ " ELSE (p.POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //+ " OR f.FIELD_POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //+ " OR pt.POS_TYPE_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //+ " OR sp.CLASS_SPEC_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //+ ") END"
                    //+ " ORDER BY p.POS_REF + 1";
                                + (posInfo.SearchText != "" ? " p.POS_NAME LIKE CONCAT('%', @SearchText , '%') "
                                + " OR f.FIELD_POS_NAME LIKE CONCAT('%', @SearchText , '%') " : "")
                                + " ORDER BY p.POS_REF + 1";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }

        public DataSet LoadPositionUpper(PositionInfo posInfo)
        {
            int jsSeq = 0;
            int etSeq = 0;
            DataSet ds;

            if (posInfo.JobSite != null && posInfo.JobSite.Sequence > 0)
            {
                jsSeq = Convert.ToInt32(posInfo.JobSite.Sequence.ToString());
            }
            if (posInfo.EmployeeType != null)
            {
                etSeq = posInfo.EmployeeType.Sequence;
            }

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", posInfo.Sequence);
                dbManager.AddParameters(2, "@JobSite", "%/" + jsSeq + "/%");
                dbManager.AddParameters(3, "@EmpTypeSeq", etSeq);
                dbManager.AddParameters(4, "@PosName", "%/" + posInfo.Name + "/%");

                string strSQL = "SELECT * FROM("
                                + " SELECT "
                                + " FROM HRM_MP_POSITIONS p1"
                                + " LEFT JOIN HRM_MP_POSITIONS p2 ON p1.upper_pos_seq = p2.POS_SEQ"
                                + " LEFT JOIN HRM_MP_POSITIONS p3 ON p2.upper_pos_seq = p3.POS_SEQ"
                                + " LEFT JOIN HRM_MP_POSITIONS p4 ON p3.upper_pos_seq = p4.POS_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEES e1 ON p1.POS_SEQ = e1.POS_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEES e2 ON p2.POS_SEQ = e2.POS_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEES e3 ON p3.POS_SEQ = e3.POS_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEES e4 ON p4.POS_SEQ = e4.POS_SEQ"
                                + ")t";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }

        public IList<PositionInfo> ListPositions(PositionInfo posInfo)
        {
            int jsSeq = 0;
            int etSeq = 0;
            List<PositionInfo> infoList = new List<PositionInfo>();

            if (posInfo.JobSite != null && posInfo.JobSite.Sequence > 0)
            {
                jsSeq = Convert.ToInt32(posInfo.JobSite.Sequence);
            }
            if (posInfo.EmployeeType != null)
            {
                etSeq = posInfo.EmployeeType.Sequence;
            }

            try 
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", posInfo.Sequence);
                dbManager.AddParameters(2, "@JobSite", "%/" + jsSeq + "/%");
                dbManager.AddParameters(3, "@EmpTypeSeq", etSeq);

                string strSQL = "SELECT p.POS_SEQ, p.POS_REF, p.POS_NAME, p.JOB_DESCRIPTION"
                                + ", p.DATE_DEFINED, p.DATE_MONEY_SUPPORT, p.SALARY_QUOTA, p.SALARY_MAX, p.SALARY_MIN"
                                + ", p.EXEC_POS_SEQ, p.EMP_TYPE_SEQ, p.JOB_SITE_SEQ, p.upper_pos_seq"
                                + ", p.working_upper_pos_seq, f.POS_TYPE_SEQ, p.POS_STATUS"  // p.CLASS_SPEC_SEQ,                     
                                + ", p.REMARKS, p.RECORD_STATUS, p.CREATED_BY, p.UPDATED_BY"
                                + " FROM HRM_MP_POSITIONS p"
                                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                                + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                                + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                                + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                                + " WHERE p.RECORD_STATUS not like @DeleteRecord"
                                + (posInfo.Sequence > 0 ? " AND p.POS_SEQ = @Sequence" : "")
                                + (etSeq > 0 ? " AND p.EMP_TYPE_SEQ = @EmpTypeSeq" : "")
                                + (jsSeq > 0 ? " AND j.JOB_SITE_FULL_PATH LIKE @JobSite" : ""); //, p.FIELD_POS_SEQ

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    FieldDAL fieldDAL = new FieldDAL();
                    FieldInfo fieldInfo = new FieldInfo();
                    if (!rdr.IsDBNull(9))
                    {
                        IList<FieldInfo> fieldList = fieldDAL.ListFields(new FieldInfo(rdr.GetInt32(9)));
                        fieldInfo = fieldList.Count > 0 ? fieldList[0] : null;
                    }
                    //*************************************************
                    ExecutiveDAL execDAL = new ExecutiveDAL();
                    ExecutiveInfo execInfo = new ExecutiveInfo();
                    if (!rdr.IsDBNull(9))
                    {
                        IList<ExecutiveInfo> execList = execDAL.ListExecutives((int?)rdr.GetInt32(9), "U");
                        execInfo = execList.Count > 0 ? execList[0] : null;
                    }
                    //*************************************************
                    EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
                    EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
                    if (!rdr.IsDBNull(10))
                    {
                        IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(10)));
                        etInfo = etList[0];
                    }
                    //**************************************************
                    JobSiteDAL jsDAL = new JobSiteDAL();
                    JobSiteInfo jsInfo = new JobSiteInfo();
                    if (!rdr.IsDBNull(11))
                    {
                        IList<JobSiteInfo> jsList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(11)));
                        jsInfo = jsList.Count > 0 ? jsList[0] : null;
                    }      
                    //**************************************************
                    PositionInfo upperInfo = new PositionInfo();
                    if (!rdr.IsDBNull(12))
                        upperInfo.Sequence = rdr.GetInt32(12);
                    //**************************************************
                    PositionInfo workingUpperInfo = new PositionInfo();
                    if (!rdr.IsDBNull(13))
                        workingUpperInfo.Sequence = rdr.GetInt32(13);
                    //**************************************************
                    //ClassSpecificationDAL specDAL = new ClassSpecificationDAL();
                    //ClassSpecificationInfo specInfo = new ClassSpecificationInfo();
                    //if (!rdr.IsDBNull(14))
                    //{
                    //    IList<ClassSpecificationInfo> specList = specDAL.ListClassSpecification(rdr.IsDBNull(14) ? (int?)null : rdr.GetInt32(14), null);
                    //    specInfo = specList.Count > 0 ? specList[0] : null;
                    //}
                    //**************************************************
                    PositionTypeInfo postypeInfo = new PositionTypeInfo();
                    PositionTypeDAL posDAL = new PositionTypeDAL();
                    if (!rdr.IsDBNull(14))
                    {
                        IList<PositionTypeInfo> posList = posDAL.ListPositionTypes(rdr.IsDBNull(14) ? (int?)null : rdr.GetInt32(14), null);
                        postypeInfo = posList.Count > 0 ? posList[0] : null;
                    }
                    //**************************************************
                    PositionStatusDAL statusDAL = new PositionStatusDAL();
                    PositionStatusInfo statusInfo = new PositionStatusInfo();
                    if (!rdr.IsDBNull(15))
                    {
                        IList<PositionStatusInfo> statusList = statusDAL.ListPositionStatus(new PositionStatusInfo(rdr.GetInt32(15)));
                        statusInfo = statusList.Count > 0 ? statusList[0] : null;
                    }

                    PositionInfo Info = new PositionInfo(rdr.GetInt32(0)
                        , rdr.IsDBNull(1) ? "" : rdr.GetInt32(1).ToString()
                        , rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                        , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                        , rdr.IsDBNull(4) ? (DateTime?)null : rdr.GetDateTime(4)
                        , rdr.IsDBNull(5) ? (DateTime?)null : rdr.GetDateTime(5)
                        , rdr.IsDBNull(6) ? (double?)null : (double?)rdr.GetDecimal(6)
                        , rdr.IsDBNull(7) ? (double?)null : (double?)rdr.GetDecimal(7)
                        , rdr.IsDBNull(8) ? (double?)null : (double?)rdr.GetDecimal(8)
                        , fieldInfo
                        , execInfo
                        , etInfo
                        , jsInfo
                        , postypeInfo
                        , upperInfo
                        , workingUpperInfo
                        , null  //specInfo
                        , null
                        , statusInfo
                        , rdr.IsDBNull(16) ? "" : rdr.GetString(16)
                        , rdr.IsDBNull(17) ? "" : rdr.GetString(17)
                        , rdr.IsDBNull(18) ? (int?)null : rdr.GetInt32(18)
                        , rdr.IsDBNull(19) ? (int?)null : rdr.GetInt32(19)   
                        );
                    infoList.Add(Info);
                }
            }
            catch(Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<TreeViewInfo> LoadPositionJobsites(Nullable<int> EmpTypeSeq, string ParentID)
        {
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@JobSiteTypeSeq", SystemInfo._JobsiteExternal);
                dbManager.AddParameters(2, "@JobSiteUpper", SystemInfo._JobsiteExternal);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@CancelRecord", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(5, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@TypeName", "โครงสร้างตามกฏกระทรวง");
                dbManager.AddParameters(7, "@parent_id", ParentID);

                strSQL = "SELECT t.* FROM(" + "\n"
                    + "SELECT CAST(j.JOB_SITE_SEQ AS CHAR) AS ID, "
                    + " j.JOB_SITE_NAME AS NAME" + "\n"
                    + ", CASE WHEN l.JOB_SITE_LEVEL_NO > 2 THEN CAST(j.upper_job_site_seq AS CHAR)" + "\n"
                    + " ELSE NULL END AS PARENT_ID" + "\n"
                    //+ ", CASE l.JOB_SITE_LEVEL_NO " + "\n"
                    //+ " WHEN 1 THEN '../Images/Buttons/xx.gif'" + "\n"
                    //+ " WHEN 2 THEN '../Images/Buttons/xx.gif'" + "\n"
                    //+ " WHEN 3 THEN '../Images/Buttons/box_green.gif'" + "\n"
                    //+ " WHEN 4 THEN '../Images/Buttons/box_blue.gif'" + "\n"
                    //+ " WHEN 5 THEN '../Images/Buttons/box_pink.gif'" + "\n"
                    //+ " ELSE '' END AS IMAGE_URL" + "\n"
                    + ", '../Images/Buttons/listItemArrow.gif' as IMAGE_URL" + "\n"
                    + ",  (select count(pos_seq) from hrm_mp_positions" + "\n"
                    + " where record_status = @ActiveRecord" + "\n"
                    + " and emp_type_seq = @EmpTypeSeq" + "\n"
                    + " and job_site_seq = j.job_site_seq)" + "\n"
                    + " + (select count(job_site_seq) from hrm_mp_job_sites" + "\n"
                    + " where record_status = @ActiveRecord" + "\n"
                    + " and upper_job_site_seq = j.job_site_seq) " + "\n"
                    + "  AS CHILDREN_COUNT" + "\n"
                    + " , l.job_site_level_name AS descr " + "\n"
                    + " FROM HRM_MP_JOB_SITES j" + "\n"
                    + " LEFT JOIN HRM_MP_JOB_SITE_LEVELS l ON j.JOB_SITE_LEVEL_SEQ = l.JOB_SITE_LEVEL_SEQ" + "\n"
                    + " WHERE j.RECORD_STATUS NOT IN (@DeleteRecord, @CancelRecord)" + "\n"
                    + " AND j.JOB_SITE_FULL_PATH LIKE (select concat('%/', curr_job_site_seq, '/%') from hrm_mp_job_site_types " + "\n"
                    + " where job_site_type_seq = @JobSiteTypeSeq)" + "\n"
                    + " AND l.JOB_SITE_LEVEL_NO > 1" + "\n"
                    + " UNION ALL" + "\n"

                    + " SELECT CAST(CONCAT('1', '-', POS_SEQ) AS CHAR) AS ID" + "\n"
                    + ", CONCAT(POS_REF, ' : ', POS_NAME) AS NAME" + "\n"
                    + ", CAST(JOB_SITE_SEQ AS CHAR) AS PARENT_ID" + "\n"
                    + ", '../Images/Buttons/Person.png' AS IMAGE_URL" + "\n"
                    + ", 0 AS CHILDREN_COUNT" + "\n"
                    + ", IIF(HRM_MP_POSITIONS.POS_STATUS = 1, 'ว่าง', 'มีคนครอง') AS descr " + "\n"
                    + " FROM HRM_MP_POSITIONS" + "\n"
                    + " where HRM_MP_POSITIONS.record_status = @ActiveRecord "
                    + " and  EMP_TYPE_SEQ = @EmpTypeSeq" + "\n"
                    + ")t" + "\n"
                    //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                    + (string.IsNullOrEmpty(ParentID) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                DataRowCollection dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();

                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        info.Descr = Convert.ToString(dr[i]["descr"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public DataTable LoadPositionWorkingJobsites(Nullable<int> EmpTypeSeq, string ParentID)
        {
            DataTable result;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@JobSiteTypeSeq", SystemInfo._JobsiteInternal);
                dbManager.AddParameters(2, "@JobSiteUpper", SystemInfo._JobsiteInternal);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@CancelRecord", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(5, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@TypeName", "โครงสร้างตามการบริหารจัดการภายใน");
                dbManager.AddParameters(7, "@parent_id", ParentID);

                string strSQL = @"SELECT tb1.ID
                                    , tb1.pos_ref
                                    , tb1.pos_name
                                    , tb1.exec_pos_name
                                    , IIF(pos1.record_status = @ActiveRecord
                                        , tb1.parent_ID
                                        , IIF(tb1.parent_ID is null, null, 0)
                                      ) as parent_ID 
                                    , IIF(pos1.record_status = @ActiveRecord OR tb1.parent_ID = 0 OR tb1.parent_ID is null
                                        , tb1.emp_name
                                        , CONCAT(tb1.emp_name, ' [ตำแหน่งที่เหนือกว่าในสายบังคับบัญชาถูกลบ]')
                                      ) as emp_name 
                                    , tb1.job_site_name
                                    , tb1.emp_type_name
                                    , tb1.internal_job_site_seq
                                    , tb1.internal_job_site_name 
                                    , CONCAT(pos1.record_status , tb1.parent_ID) as parentrecordstatusandid
                                    , IIF(tb1.exec_pos_name not like '', CONCAT('(', tb1.exec_pos_name , ')'), '') as exec_pos_name_parentheses
                                    , tb1.emp_seq 
                                    , tb1.workOnDutyPopupHtml
                                    , tb1.image_name
                                  FROM (
                                    select 0 as ID, '' as pos_ref, 'สายการบังคับบัญชา' as pos_name 
                                        , 'โครงสร้างบริหารจัดการภายใน' as exec_pos_name
                                        , null as parent_ID
                                        , '' as emp_name
                                        , '' as job_site_name
                                        ,'' as emp_type_name
                                        ,'' as internal_job_site_seq
                                        ,'' as internal_job_site_name
                                        , '' as emp_seq
                                        , '' as workOnDutyPopupHtml
                                        , '' as image_name
                                    union all

                                    select p.pos_seq as ID, p.pos_ref, p.pos_name, exe.exec_pos_name
                                        , coalesce(p.working_upper_pos_seq, 0) as parent_ID
                                        , case when e.emp_seq is null
                                        then 'ไม่มีคนครอง'
                                        else cast(concat(np.prefix_short, e.first_name, ' ', e.last_name) as char)
                                        end as emp_name
                                        , j.job_site_name, et.emp_type_name
                                        , j_internal.job_site_seq as internal_job_site_seq
                                        , IIF(j_internal.job_site_name is null OR j_internal.job_site_name = '' 
                                            ,'-'
                                            ,j_internal.job_site_name
                                          ) as internal_job_site_name
                                        , e.emp_seq as emp_seq
                                        , STUFF((
          SELECT CONCAT(',', wod.wod_seq)
          FROM hrm_mp_position_work_on_duty wod
          WHERE e.emp_seq = wod.to_pos_seq and (GETDATE() Between wod.start_date and wod.end_date) and wod.record_status != 'D'
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as workOnDutyPopupHtml 
                                        , e.image_unique as image_name
                                    from hrm_mp_positions p
                                        left join hrm_ps_employee_types et 
                                            on p.emp_type_seq = et.emp_type_seq
                                        left join hrm_mp_exec_positions exe 
                                            on p.exec_pos_seq = exe.exec_pos_seq
                                        left join hrm_ps_employees e 
                                            on p.pos_seq = e.pos_seq
                                                and e.record_status not in (@DeleteRecord, @CancelRecord)
                                                and e.emp_status_seq in (
                                                    select emp_status_seq from hrm_ps_employee_statuses where active = '1'
                                                )
                                        left join mas_name_prefixes np 
                                            on e.name_prefix_seq = np.name_prefix_seq
                                        left join hrm_mp_job_sites j 
                                            on p.job_site_seq = j.job_site_seq                                
                                        LEFT JOIN hrm_mp_job_sites j_internal 
                                            ON e.working_job_site_seq = j_internal.job_site_seq
                                    where p.record_status not in (@DeleteRecord, @CancelRecord)
                                 ) as tb1
                                    LEFT JOIN hrm_mp_positions pos1 
                                        ON pos1.pos_seq = tb1.parent_ID ";
                result = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                var ds = (from DataRow dr in result.Rows select dr).ToList(); 
            }
            catch (Exception error)
            {
                throw;
                /* -- Script for create table 'hrm_mp_position_work_on_duty'.
                USE [GERP]
                GO
                 
                SET ANSI_NULLS ON
                GO

                SET QUOTED_IDENTIFIER ON
                GO

                CREATE TABLE [dbo].[hrm_mp_position_work_on_duty](
	                [wod_seq] [int] IDENTITY(1,1) NOT NULL,
	                [to_emp_seq] [int] NOT NULL,
	                [by_emp_seq] [int] NOT NULL,
	                [start_date] [datetime2](7) NOT NULL,
	                [end_date] [datetime2](7) NOT NULL,
	                [see_old_message] [bit] NOT NULL,
	                [description] [nvarchar](max) NULL,
	                [record_status] [nvarchar](50) NOT NULL,
	                [created_by] [int] NOT NULL,
	                [created_when] [datetime2](7) NOT NULL,
	                [updated_by] [int] NULL,
	                [updated_when] [datetime2](7) NULL,
                    CONSTRAINT [PK_hrm_mp_position_work_on_duty] PRIMARY KEY CLUSTERED 
                (
	                [wod_seq] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

                GO

                ALTER TABLE [dbo].[hrm_mp_position_work_on_duty] ADD  CONSTRAINT [DF_hrm_mp_position_work_on_duty_see_old_message]  DEFAULT ((0)) FOR [see_old_message]
                GO

                ALTER TABLE [dbo].[hrm_mp_position_work_on_duty] ADD  CONSTRAINT [DF_hrm_mp_position_work_on_duty_description]  DEFAULT ('') FOR [description]
                GO
                 * 
                 */
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }

        public EmployeeInfo LoadEmployeeOfPosition(int positionSeq)
        {
            EmployeeInfo result;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@CancelRecord", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(2, "@PositionSeq", positionSeq);

                string strSQL = @"
                                    SELECT e.*
                                    FROM hrm_mp_positions p
                                    INNER JOIN hrm_ps_employees e ON p.pos_seq = e.pos_seq
                                      AND e.record_status NOT IN (@DeleteRecord, @CancelRecord)
                                      AND e.emp_status_seq IN (SELECT emp_status_seq FROM hrm_ps_employee_statuses WHERE active = '1')
                                    WHERE p.record_status NOT IN (@DeleteRecord, @CancelRecord)
                                      and p.pos_seq = @PositionSeq";

                DataRowCollection dr = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (dr.Count > 0)
                {
                    DataRow employeeRow = dr[0];
                    result = new EmployeeInfo((int) employeeRow["emp_seq"]);
                    result.Person = new PersonInfo
                                        {
                                            FirstName = (string) employeeRow["first_name"],
                                            LastName = (string) employeeRow["last_name"]
                                        };
                }
                else
                {
                    result = null;
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }

        public IList<TreeViewInfo> LoadPositionWorkingJobsites_EditedThenProblem(Nullable<int> EmpTypeSeq, string ParentID)
        {
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@JobSiteTypeSeq", SystemInfo._JobsiteInternal);
                dbManager.AddParameters(2, "@JobSiteUpper", SystemInfo._JobsiteInternal);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@CancelRecord", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(5, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@TypeName", "โครงสร้างตามการบริหารจัดการภายใน");
                dbManager.AddParameters(7, "@parent_id", ParentID);

                string strSQL = "SELECT t.ID, t.SEQ, t.NAME, "
                                + "IIF( t.PARENT_ID = '', NULL, IIF(t.PARENT_ID = '1-2', NULL, t.PARENT_ID)) PARENT_ID"
                                + ", t.IMAGE_URL, t.CHILDREN_COUNT, t.TREE_TYPE, t.DESCRIPTION  FROM("
                                + " SELECT CAST(CONCAT(@EmpTypeSeq, '-', j.JOB_SITE_SEQ) AS CHAR) AS ID"
                                + ", j.JOB_SITE_SEQ AS SEQ"
                                + ", j.JOB_SITE_NAME AS NAME"
                                + ", CASE WHEN j.upper_job_site_seq IS NOT NULL THEN "
                                + " CAST(CONCAT(@EmpTypeSeq, '-', j.upper_job_site_seq)AS CHAR)"
                                + " ELSE NULL END AS PARENT_ID"
                                + ", CASE l.JOB_SITE_LEVEL_NO "
                                + " WHEN 1 THEN '../Images/Buttons/xx.gif'"
                                + " WHEN 2 THEN '../Images/Buttons/xx.gif'"
                                + " WHEN 3 THEN '../Images/Buttons/box_green.gif'"
                                + " WHEN 4 THEN '../Images/Buttons/box_blue.gif'"
                                + " ELSE '' END AS IMAGE_URL"
                                + ", (select count(pos_seq) "
                                + " from hrm_ps_employees e"
                                + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                                + " where e.record_status = @ActiveRecord"
                                + " and es.active not like 0"
                                + " and e.emp_type_seq = @EmpTypeSeq"
                                + " and e.working_job_site_seq = j.job_site_seq)"
                                + " AS CHILDREN_COUNT"
                                + ", CAST('J' AS CHAR) AS TREE_TYPE"
                                + ", NULL AS DESCRIPTION"
                                + " FROM HRM_MP_JOB_SITES j"
                                + " LEFT JOIN HRM_MP_JOB_SITE_LEVELS l ON j.JOB_SITE_LEVEL_SEQ = l.JOB_SITE_LEVEL_SEQ"
                                + " WHERE j.RECORD_STATUS NOT IN (@DeleteRecord, @CancelRecord)"
                                + " AND j.JOB_SITE_SEQ = (select curr_job_site_seq from hrm_mp_job_site_types "
                                + " where job_site_type_seq = @JobSiteTypeSeq)"
                                + " AND l.JOB_SITE_LEVEL_NO > 1"
                                + " UNION ALL"

                                + " SELECT CAST(CONCAT(e.EMP_TYPE_SEQ, '-', e.POS_SEQ) AS CHAR) AS ID"
                                + ", p.POS_REF AS SEQ"
                                + ", CONCAT(p.POS_REF, ' : ', IFNULL(ex.EXEC_POS_NAME, p.POS_NAME), '[', IFNULL(wj.JOB_SITE_NAME, 'ไม่ระบุสังกัดปฏิบัติ'), ']') AS NAME"
                                + ", CAST(CONCAT(e.EMP_TYPE_SEQ, '-'"
                                + ", IFNULL(p.working_upper_pos_seq, CASE WHEN e.POS_SEQ = 2 THEN e.WORKING_JOB_SITE_SEQ ELSE 2 END)"
                                + ") AS CHAR) AS PARENT_ID"
                                + ", '../Images/Buttons/account.gif' AS IMAGE_URL"
                                + ", (select count(e.pos_seq) "
                                + " from hrm_ps_employees e"
                                + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                                + " left join hrm_mp_positions sp on e.pos_seq = sp.pos_seq"
                                + " where e.record_status = @ActiveRecord"
                                + " and es.active not like 0"
                                + " and e.emp_type_seq = @EmpTypeSeq"
                                + " and sp.working_upper_pos_seq = p.pos_seq)"
                                + " AS CHILDREN_COUNT"
                                + ", CAST('P'AS CHAR) AS TREE_TYPE"
                                + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS DESCRIPTION"
                                + " FROM HRM_PS_EMPLOYEES e"
                                + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
                                + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                                + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                                + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                                + " LEFT JOIN HRM_MP_JOB_SITE_LEVELS wl ON wj.JOB_SITE_LEVEL_SEQ = wl.JOB_SITE_LEVEL_SEQ"
                                + " WHERE e.EMP_TYPE_SEQ = @EmpTypeSeq"
                                + " AND es.ACTIVE not like 0"
                                + ")t"
                                + " /*WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END */"
                                + " ORDER BY t.SEQ + 1";

                DataRowCollection dr = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();

                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<PositionInfo> LoadPositionTreeView(Nullable<int> EmpTypeSeq)
        {
            IList<PositionInfo> infoList = new List<PositionInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                string strSQL = "SELECT pos.* FROM("
                                + " SELECT 0 AS POS_SEQ, NULL AS POS_REF"
                                + ", 'แบ่งตามกฏกระทรวง' AS POS_NAME"
                                + ", NULL AS POS_STATUS_NAME, NULL AS upper_pos_seq"
                                + ", 'แบ่งตามกฏกระทรวง' AS POS_TEXT, NULL AS JOB_DESCRIPTION"
                                + " UNION ALL"
                                + " SELECT p.POS_SEQ, p.POS_REF, CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_NAME"
                                + ", CASE WHEN e.POS_SEQ IS NULL THEN CAST('ว่าง' AS CHAR)"
                                + " ELSE 'มีคนครอง' END AS POS_STATUS_NAME"
                                + ", CASE WHEN ISNULL(p.upper_pos_seq) THEN 0 ELSE p.upper_pos_seq END AS upper_pos_seq"
                                + ", CAST(CONCAT(CAST(p.POS_REF AS CHAR), ' - '"
                                + ", CASE WHEN p.EXEC_POS_SEQ = 0 OR ISNULL(p.EXEC_POS_SEQ) THEN CAST(f.FIELD_POS_LINE AS CHAR)"
                                + " ELSE CAST(CONCAT(ex.EXEC_POS_NAME"
                                + ", CASE WHEN f.POS_TYPE_SEQ = 4 THEN '' "
                                + " ELSE j.JOB_SITE_NAME "
                                + "END) AS CHAR)"
                                + " END"
                                + ") AS CHAR) AS POS_TEXT"
                                + ", p.JOB_DESCRIPTION"
                                + " FROM HRM_MP_POSITIONS p"
                                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                                + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                                + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEES e ON p.POS_SEQ = e.POS_SEQ AND e.EMP_STATUS_SEQ = 1"
                                + " WHERE p.RECORD_STATUS = @ActiveRecord"
                                + " AND f.EMP_TYPE_SEQ = @EmpTypeSeq"
                                + ")pos "
                                + " ORDER BY pos.POS_REF + 0";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                DataRowCollection dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PositionInfo info = new PositionInfo();
                        info.Sequence = Int32.Parse(dr[i]["POS_SEQ"].ToString());
                        info.RefNo = Convert.IsDBNull(dr[i]["POS_REF"]) ? "" : (dr[i]["POS_REF"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["POS_TEXT"]) ? "" : (dr[i]["POS_TEXT"]).ToString();
                        info.Parent = Convert.IsDBNull(dr[i]["upper_pos_seq"]) ? (int?)null : Int32.Parse(dr[i]["upper_pos_seq"].ToString());
                        info.JobDescription = Convert.IsDBNull(dr[i]["JOB_DESCRIPTION"]) ? "" : (dr[i]["JOB_DESCRIPTION"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<PositionInfo> LoadWorkingTreeView(Nullable<int> EmpTypeSeq)
        {
            IList<PositionInfo> infoList = new List<PositionInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                string strSQL = "SELECT pos.* FROM("
                                + " SELECT 0 AS POS_SEQ, NULL AS POS_REF"
                                + ", 'แบ่งตามการบริหารจัดการ' AS POS_NAME"
                                + ", NULL AS POS_STATUS_NAME, NULL AS upper_pos_seq"
                                + ", 'แบ่งตามการบริหารจัดการ' AS POS_TEXT, NULL AS JOB_DESCRIPTION"
                                + " UNION ALL"
                                + " SELECT p.POS_SEQ, p.POS_REF, CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_NAME"
                                + ", CASE WHEN e.POS_SEQ IS NULL THEN CAST('ว่าง' AS CHAR)"
                                + " ELSE 'มีคนครอง' END AS POS_STATUS_NAME"
                                + ", CASE WHEN ISNULL(p.working_upper_pos_seq) THEN 0 ELSE p.working_upper_pos_seq END AS upper_pos_seq"
                                + ", CAST(CONCAT(p.POS_REF, ' - '"
                                + ", CASE WHEN p.EXEC_POS_SEQ = 0 OR ISNULL(p.EXEC_POS_SEQ) THEN CAST(p.POS_NAME AS CHAR)"
                                + " ELSE CAST(CONCAT(ex.EXEC_POS_NAME"
                                + ", CASE WHEN f.POS_TYPE_SEQ = 4 THEN '' ELSE j.JOB_SITE_NAME END) AS CHAR)"
                                + " END"
                                + ")AS CHAR) AS POS_TEXT"
                                + ", p.JOB_DESCRIPTION"
                                + " FROM HRM_MP_POSITIONS p"
                                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                                + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                                + " LEFT JOIN HRM_PS_EMPLOYEES e ON p.POS_SEQ = e.POS_SEQ "
                                + " LEFT JOIN HRM_MP_JOB_SITES j ON e.WORKING_JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                                + " WHERE p.RECORD_STATUS = @ActiveRecord"
                                + " AND f.EMP_TYPE_SEQ = @EmpTypeSeq"
                                + " AND e.EMP_SEQ IS NOT NULL"
                                + ")pos "
                                + " ORDER BY POS_REF + 0";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    PositionInfo info = new PositionInfo();
                    info.Sequence = rdr.GetInt32(0);
                    info.RefNo = rdr.IsDBNull(1) ? "" : rdr.GetString(1);
                    info.Name = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
                    info.Parent = rdr.IsDBNull(4) ? (int?)null : rdr.GetInt32(4);
                    infoList.Add(info);
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

		public Dictionary<string, string> GetJobSiteAndPositionTypeAndClassByPosition(string positionSeq)
		{
			Dictionary<string, string> result = new Dictionary<string, string>
				{
					{"pos_seq", ""},
					{"pos_name", ""},
					{"field_pos_seq", ""},
					{"field_pos_name", ""},
					{"pos_type_seq", ""},
					{"pos_type_name", ""},
					{"class_seq", ""},
					{"job_site_seq", ""},
					{"job_site_name", ""}
				};

			if (string.IsNullOrEmpty(positionSeq)) return result;

			try
			{
				dbManager.Open();
				dbManager.BeginTransaction();
				dbManager.CreateParameters(2);

				dbManager.AddParameters(0, "@UsedStatus", SystemInfo._ActiveRecordStatus);
				dbManager.AddParameters(1, "@PosSeq", positionSeq);

                /*
				string strSQL = @"
SELECT p.pos_seq, p.pos_name, f.field_pos_seq, f.field_pos_name, t.pos_type_seq, t.pos_type_name, j.job_site_seq, j.job_site_name, p.class_seq
FROM hrm_mp_positions p 
LEFT JOIN hrm_mp_field_positions f ON f.field_pos_seq = p.field_pos_seq AND f.record_status = @UsedStatus
LEFT JOIN hrm_mp_position_types t ON t.pos_type_seq = p.pos_type_seq AND t.record_status = @UsedStatus
LEFT JOIN hrm_mp_job_sites j ON j.job_site_seq = p.job_site_seq AND j.record_status = @UsedStatus
WHERE p.pos_seq = @PosSeq
AND p.record_status = @UsedStatus";
                */

                string strSQL = @"
SELECT p.pos_seq, p.pos_name, f.field_pos_seq, f.field_pos_name, j.job_site_seq, j.job_site_name, t.pos_type_seq, 
t.pos_type_name, cs.class_spec_seq, cs.class_spec_name, c.class_seq, c.class_name
FROM hrm_mp_positions p 
LEFT JOIN hrm_mp_field_positions f ON f.field_pos_seq = p.field_pos_seq AND f.record_status = @UsedStatus
LEFT JOIN hrm_mp_job_sites j ON j.job_site_seq = p.job_site_seq AND j.record_status = @UsedStatus
LEFT JOIN hrm_mp_position_types t ON t.pos_type_seq = f.pos_type_seq AND t.record_status = @UsedStatus
LEFT JOIN hrm_mp_class_specifications cs on f.class_spec_seq = cs.class_spec_seq and cs.record_status = @UsedStatus
LEFT JOIN hrm_mp_class_spec_dtls cd on cd.class_spec_seq = cs.class_spec_seq and cd.record_status = @UsedStatus
LEFT JOIN hrm_mp_classes c on c.class_seq = cd.class_seq and c.record_status = @UsedStatus
WHERE p.pos_seq = @PosSeq
AND p.record_status = @UsedStatus";

				DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
				if (dt.Rows.Count > 0)
				{
					DataRow dr = dt.Rows[0];
					result["pos_seq"] = Convert.ToString(dr["pos_seq"]);
					result["pos_name"] = Convert.ToString(dr["pos_name"]);
					result["field_pos_seq"] = Convert.ToString(dr["field_pos_seq"]);
					result["field_pos_name"] = Convert.ToString(dr["field_pos_name"]);
					result["pos_type_seq"] = Convert.ToString(dr["pos_type_seq"]);
					result["pos_type_name"] = Convert.ToString(dr["pos_type_name"]);
                    result["class_seq"] = Convert.ToString(dr["class_seq"]);
					result["job_site_seq"] = Convert.ToString(dr["job_site_seq"]);
					result["job_site_name"] = Convert.ToString(dr["job_site_name"]);
				}
			}
			catch (Exception error)
			{
				throw error;
			}
            finally
            {
                dbManager.Dispose();
            }

			return result;
		}
        
        public void Insert(PositionInfo Info)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@PosRef", Info.RefNo);
                dbManager.AddParameters(1, "@PosName", Info.Name);
                dbManager.AddParameters(2, "@FieldPosSeq", Info.Field == null ? (int?)null : Info.Field.Sequence);
                dbManager.AddParameters(3, "@ExecPosSeq", Info.Executive == null ? (int?)null : Info.Executive.Sequence);
                dbManager.AddParameters(4, "@EmpTypeSeq", Info.EmployeeType == null ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(5, "@DateDefined", Info.DefinedDate);
                dbManager.AddParameters(6, "@DateMoneySubport", Info.MoneySubportDate);
                dbManager.AddParameters(7, "@SalaryQuota", Info.SalaryQuota);
                dbManager.AddParameters(8, "@JobSiteSeq", Info.JobSite == null ? (int?)null : Info.JobSite.Sequence);
                dbManager.AddParameters(9, "@PosStatus", Info.Status == null ? (int?)null : Info.Status.Sequence);
                dbManager.AddParameters(10, "@SalaryMax", Info.SalaryMax);
                dbManager.AddParameters(11, "@SalaryMin", Info.SalaryMin);
                dbManager.AddParameters(12, "@PosUpperSeq", Info.Upper == null ? (int?)null : Info.Upper.Sequence);
				dbManager.AddParameters(13, "@PosWorkingUpperSeq", Info.WorkingUpper == null ? (int?)null : Info.WorkingUpper.Sequence);
				dbManager.AddParameters(14, "@ClassSpecSeq", Info.ClassSpecification == null ? (int?)null : Info.ClassSpecification.Sequence);
                dbManager.AddParameters(15, "@JobDescr", Info.JobDescription);
                dbManager.AddParameters(16, "@Remarks", Info.Remarks);
                dbManager.AddParameters(17, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(18, "@CreatedBy", Info.CreatedBy);
				dbManager.AddParameters(19, "@CreatedWhen", DateTime.Today);
				dbManager.AddParameters(20, "@PosTypeSeq", Info.PositionType == null ? (int?)null : Info.PositionType.Sequence);
				dbManager.AddParameters(21, "@ClassSeq", Info.ClassSeq);

// ตัดฟิลด์ CLASS_SEQ, POS_TYPE_SEQ ออก
//                string strSQL = @"	INSERT INTO HRM_MP_POSITIONS(
//										POS_REF, POS_NAME , FIELD_POS_SEQ, EXEC_POS_SEQ, EMP_TYPE_SEQ
//										, DATE_DEFINED, DATE_MONEY_SUPPORT, SALARY_QUOTA, JOB_SITE_SEQ, SALARY_MAX
//										, SALARY_MIN, upper_pos_seq, CLASS_SEQ, JOB_DESCRIPTION
//										, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN, POS_TYPE_SEQ
//									) VALUES ( 
//										@PosRef, @PosName, @FieldPosSeq, @ExecPosSeq, @EmpTypeSeq
//										, @DateDefined, @DateMoneySubport, @SalaryQuota, @JobSiteSeq, @SalaryMax
//										, @SalaryMin, @PosUpperSeq , @ClassSeq, @JobDescr, 
//										@Remarks, @RecordStatus, @CreatedBy, @CreatedWhen, @PosTypeSeq
//									)";

                string strSQL = @"	INSERT INTO HRM_MP_POSITIONS(
										POS_REF, POS_NAME , FIELD_POS_SEQ, EXEC_POS_SEQ, EMP_TYPE_SEQ
										, DATE_DEFINED, DATE_MONEY_SUPPORT, SALARY_QUOTA, JOB_SITE_SEQ, SALARY_MAX
										, SALARY_MIN, upper_pos_seq, JOB_DESCRIPTION
										, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN
									) VALUES ( 
										@PosRef, @PosName, @FieldPosSeq, @ExecPosSeq, @EmpTypeSeq
										, @DateDefined, @DateMoneySubport, @SalaryQuota, @JobSiteSeq, @SalaryMax
										, @SalaryMin, @PosUpperSeq , @JobDescr, 
										@Remarks, @RecordStatus, @CreatedBy, @CreatedWhen
									)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(PositionInfo Info)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@PosRef", Info.RefNo);
                dbManager.AddParameters(1, "@PosName", Info.Name);
                dbManager.AddParameters(2, "@FieldPosSeq", Info.Field == null ? (int?)null : Info.Field.Sequence);
                dbManager.AddParameters(3, "@ExecPosSeq", Info.Executive == null ? (int?)null : Info.Executive.Sequence);
                dbManager.AddParameters(4, "@EmpTypeSeq", Info.EmployeeType == null ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(5, "@DateDefined", Info.DefinedDate);
                dbManager.AddParameters(6, "@DateMoneySubport", Info.MoneySubportDate);
                dbManager.AddParameters(7, "@SalaryQuota", Info.SalaryQuota);
                dbManager.AddParameters(8, "@JobSiteSeq", Info.JobSite == null ? (int?)null : Info.JobSite.Sequence);
                dbManager.AddParameters(9, "@PosStatus", Info.Status == null ? (int?)null : Info.Status.Sequence);
                dbManager.AddParameters(10, "@SalaryMax", Info.SalaryMax);
                dbManager.AddParameters(11, "@SalaryMin", Info.SalaryMin);
                dbManager.AddParameters(12, "@PosUpperSeq", Info.Upper == null ? (int?)null : Info.Upper.Sequence);
                dbManager.AddParameters(13, "@PosWorkingUpperSeq", Info.WorkingUpper == null ? (int?)null : Info.WorkingUpper.Sequence);
                dbManager.AddParameters(14, "@ClassSpecSeq", Info.ClassSpecification == null ? (int?)null : Info.ClassSpecification.Sequence);
                dbManager.AddParameters(15, "@JobDescr", Info.JobDescription);
                dbManager.AddParameters(16, "@Remarks", Info.Remarks);
                dbManager.AddParameters(17, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(18, "@UpdatedWhen", DateTime.Today);
				dbManager.AddParameters(19, "@PosSeq", Info.Sequence);
				dbManager.AddParameters(20, "@PosTypeSeq", Info.PositionType == null ? (int?)null : Info.PositionType.Sequence);
				dbManager.AddParameters(21, "@ClassSeq", Info.ClassSeq);

                string strSQL = "UPDATE HRM_MP_POSITIONS"
                                + " SET POS_REF = @PosRef"
                                + ", POS_NAME = @PosName"
                                + ", FIELD_POS_SEQ = @FieldPosSeq"
                                + ", EXEC_POS_SEQ =  @ExecPosSeq"
                                + ", EMP_TYPE_SEQ = @EmpTypeSeq"
                                + ", DATE_DEFINED = @DateDefined"
                                + ", DATE_MONEY_SUPPORT = @DateMoneySubport"
                                + ", SALARY_QUOTA = @SalaryQuota"
                                + ", JOB_SITE_SEQ = @JobSiteSeq"
                                + ", POS_STATUS = @PosStatus"
                                + ", SALARY_MAX = @SalaryMax"
                                + ", SALARY_MIN = @SalaryMin"
								+ ", upper_pos_seq = @PosUpperSeq"
								//+ ", CLASS_SPEC_SEQ = @ClassSpecSeq"
								//+ ", CLASS_SEQ = @ClassSeq"
								+ ", JOB_DESCRIPTION = @JobDescr"
								//+ ", POS_TYPE_SEQ = @PosTypeSeq"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                                + " WHERE POS_SEQ = @PosSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string strSQL = "UPDATE HRM_MP_POSITIONS"
                                + " SET RECORD_STATUS = @record_status"
                                + ", UPDATED_BY = @updated_by"
                                + ", UPDATED_WHEN = @updated_when"
                                + " WHERE POS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateUpper(int Sequence, string Upper, int UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Sequence);
                dbManager.AddParameters(1, "@upper_seq", Upper.Length > 0 ? Int32.Parse(Upper) : (int?)null);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string strSQL = "UPDATE HRM_MP_POSITIONS"
                                + " SET upper_pos_seq = @upper_seq"
                                + ", UPDATED_BY = @updated_by"
                                + ", UPDATED_WHEN = @updated_when"
                                + " WHERE POS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateWorkingUpper(int Sequence, string WorkingUpper, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Sequence);
                dbManager.AddParameters(1, "@working_upper_seq", WorkingUpper);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_MP_POSITIONS"
                    + " SET working_upper_pos_seq = @working_upper_seq"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE POS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateJobSites(int Sequence, string JobSiteSeq, int UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@JobSiteSeq", JobSiteSeq.Length > 0 ? Int32.Parse(JobSiteSeq) : (int?)null);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string strSQL = "UPDATE HRM_MP_POSITIONS"
                                + " SET JOB_SITE_SEQ = @JobSiteSeq"
                                + ", UPDATED_BY = @updated_by"
                                + ", UPDATED_WHEN = @updated_when"
                                + " WHERE POS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        
        private int? ConvertIntNull(object o)
        {
            int r = 0;
            if(Int32.TryParse((o ?? "").ToString(), out r))
            {
                return r;
            } else
            {
                return (int?) null;
            } 
        }

        private DateTime? ConvertDateTimeNull(object o)
        {
            DateTime r;
            if(DateTime.TryParse((o ?? "").ToString(), out r))
            {
                return r;
            } else
            {
                return (DateTime?) r;
            }
        }
        
        public List<WorkOnDuty> SelectWorkOnDuty()
        {
            var result = new List<WorkOnDuty>();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@delete_recordstatus", "D");
                
                string strSQL = @"
SELECT d.wod_seq
      ,d.ref_no
      ,d.to_pos_seq
      ,d.by_emp_seq
      ,CONCAT(COALESCE(p1.pos_ref, ''), ' ', COALESCE(p1.pos_name, ''), ' ',COALESCE(n1.prefix_name, ''), COALESCE(e1.first_name, ''), ' ', COALESCE(e1.last_name,'')) as to_emp_name 
      ,CONCAT(COALESCE(p2.pos_ref, ''), ' ', COALESCE(p2.pos_name, ''), ' ',COALESCE(n2.prefix_name, ''), COALESCE(e2.first_name, ''), ' ', COALESCE(e2.last_name,'')) as by_emp_name 
      ,d.start_date
      ,d.end_date
      ,d.see_old_message
      ,d.record_status
      ,d.created_by
      ,d.created_when
      ,d.updated_by
      ,d.updated_when
      ,d.description
  FROM hrm_mp_position_work_on_duty d
    LEFT JOIN hrm_ps_employees e1 ON (d.to_pos_seq = e1.pos_seq and e1.record_status != 'D')
    LEFT JOIN mas_name_prefixes n1 ON e1.name_prefix_seq = n1.name_prefix_seq
    LEFT JOIN hrm_mp_positions p1 ON d.to_pos_seq = p1.pos_seq
    LEFT JOIN hrm_ps_employees e2 ON d.by_emp_seq = e2.emp_seq
    LEFT JOIN mas_name_prefixes n2 ON e2.name_prefix_seq = n2.name_prefix_seq
    LEFT JOIN hrm_mp_positions p2 ON e2.pos_seq = p2.pos_seq
  WHERE d.record_status != @delete_recordstatus 
  /* and n1.record_status != @delete_recordstatus  
  and n2.record_status != @delete_recordstatus
  and p1.record_status != @delete_recordstatus */
";

                DataTable dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                if(dt != null)
                {
                    foreach(DataRow dr in dt.Rows)
                    {
                        var wod = new WorkOnDuty();
                        wod.wod_seq = Convert.ToInt32(dr["wod_seq"]);
                        wod.ref_no = Convert.ToString(dr["ref_no"]);
                        wod.to_pos_seq = Convert.ToInt32(dr["to_pos_seq"]);
                        wod.by_emp_seq = Convert.ToInt32(dr["by_emp_seq"]);
                        wod.to_emp_name = Convert.ToString(dr["to_emp_name"]);
                        wod.by_emp_name = Convert.ToString(dr["by_emp_name"]);
                        wod.start_date = Convert.ToDateTime(dr["start_date"]);
                        wod.end_date = Convert.ToDateTime(dr["end_date"]);
                        wod.see_old_message = Convert.ToBoolean(dr["see_old_message"]);
                        wod.description = Convert.ToString(dr["description"]);
                        wod.record_status = Convert.ToString(dr["record_status"]);
                        wod.created_by = Convert.ToInt32(dr["created_by"]);
                        wod.created_when = Convert.ToDateTime(dr["created_when"]);
                        wod.updated_by = ConvertIntNull(dr["updated_by"]);
                        wod.updated_when = ConvertDateTimeNull(dr["updated_when"]);
                        result.Add(wod);
                    }
                }
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }
        
        public string GetWodNextCode()
        {
            int buddishtYear = new ThaiBuddhistCalendar().GetYear(DateTime.Now);
            string strCodeDate = buddishtYear.ToString("0000").Substring(DateTime.Today.Year.ToString("0000").Length - 2)
                + DateTime.Today.Month.ToString("00");
            string strResult = "";
            string strPrefix = "";
            string strMaxCode = "";

            strPrefix = "WD";
            strMaxCode = SelectWorkOnDuty().ToList().Where(m => (m.ref_no != null ? m.ref_no : "").StartsWith(strPrefix + strCodeDate)).Max(m => m.ref_no);
            
            if (string.IsNullOrEmpty(strMaxCode))
            {
                strResult = strPrefix + strCodeDate + "0001";
            }
            else
            {
                var strRunning = strMaxCode.Substring(strMaxCode.Length - 4);
                int nRunning;
                if (int.TryParse(strRunning, out nRunning))
                {
                    strResult = strPrefix + strCodeDate + (nRunning + 1).ToString("0000");
                }
                else
                {
                    strResult = strPrefix + strCodeDate + "0001";
                }
            }
            
            return strResult;
        }

        public WorkOnDuty InsertWorkOnDuty(WorkOnDuty wod)
        {
            WorkOnDuty result = null;
            int lastestWod = 0;
            wod.ref_no = GetWodNextCode();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@to_pos_seq", wod.to_pos_seq);
                dbManager.AddParameters(1, "@by_emp_seq", wod.by_emp_seq);
                dbManager.AddParameters(2, "@start_date", wod.start_date);
                dbManager.AddParameters(3, "@end_date", wod.end_date);
                dbManager.AddParameters(4, "@created_by", wod.created_by);
                dbManager.AddParameters(5, "@see_old_message", wod.see_old_message);
                dbManager.AddParameters(6, "@description", wod.description);
                dbManager.AddParameters(7, "@ref_no", wod.ref_no);

                string strSQL = @"
INSERT INTO [dbo].[hrm_mp_position_work_on_duty]
           ([ref_no]
           ,[to_pos_seq]
           ,[by_emp_seq]
           ,[start_date]
           ,[end_date]
           ,[see_old_message]
           ,[description]
           ,[record_status]
           ,[created_by]
           ,[created_when])
     VALUES
           (@ref_no
           ,@to_pos_seq
           ,@by_emp_seq
           ,@start_date
           ,@end_date
           ,@see_old_message
           ,@description
           ,'W'
           ,@created_by
           ,GETDATE());

SELECT SCOPE_IDENTITY();
";

                var dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                if(dt != null && dt.Rows.Count > 0)
                {
                    lastestWod = Convert.ToInt32(dt.Rows[0][0]);
                }
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                if(dbManager != null && dbManager.Connection != null)
                dbManager.RollBackTransaction();
                throw;
            }

            finally
            {
                if(dbManager != null && dbManager.Connection != null)
                dbManager.Dispose();
            }
            result = SelectWorkOnDuty().FirstOrDefault(o=>o.wod_seq == lastestWod);
            return result;
        }

        public WorkOnDuty UpdateWorkOnDuty(WorkOnDuty wod)
        {
            WorkOnDuty result = null;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@to_pos_seq", wod.to_pos_seq);
                dbManager.AddParameters(1, "@by_emp_seq", wod.by_emp_seq);
                dbManager.AddParameters(2, "@start_date", wod.start_date);
                dbManager.AddParameters(3, "@end_date", wod.end_date);
                dbManager.AddParameters(4, "@wod_seq", wod.wod_seq);
                dbManager.AddParameters(5, "@updated_by", wod.updated_by);
                dbManager.AddParameters(6, "@see_old_message", wod.see_old_message);
                dbManager.AddParameters(7, "@description", wod.description);
                dbManager.AddParameters(8, "@ref_no", wod.ref_no);

                string strSQL = @"
UPDATE [dbo].[hrm_mp_position_work_on_duty]
   SET [ref_no] = @ref_no
      ,[to_pos_seq] = @to_pos_seq
      ,[by_emp_seq] = @by_emp_seq
      ,[start_date] = @start_date
      ,[end_date] = @end_date
      ,[see_old_message] = @see_old_message
      ,[description] = @description
      ,[updated_by] = @updated_by
      ,[updated_when] = GETDATE()
 WHERE wod_seq = @wod_seq
";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                if (dbManager != null && dbManager.Connection != null)
                    dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                if (dbManager != null && dbManager.Connection != null)
                    dbManager.Dispose();
            }
            result = SelectWorkOnDuty().FirstOrDefault(o => o.wod_seq == wod.wod_seq);
            return result;
        }

        public WorkOnDuty DeleteWorkOnDuty(WorkOnDuty wod)
        {
            WorkOnDuty result = null;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@wod_seq", wod.wod_seq);
                dbManager.AddParameters(1, "@updated_by", wod.updated_by);

                string strSQL = @"
UPDATE [dbo].[hrm_mp_position_work_on_duty]
   SET record_status = 'D',
    updated_by = @updated_by,
    updated_when = GETDATE()
 WHERE wod_seq = @wod_seq
";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                if (dbManager != null && dbManager.Connection != null)
                    dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                if (dbManager != null && dbManager.Connection != null)
                    dbManager.Dispose();
            }
            result = SelectWorkOnDuty().FirstOrDefault(o => o.wod_seq == wod.wod_seq);
            return result;
        }
    }
}
